package com.ruikong.dazhi.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import sun.misc.BASE64Decoder;
import sun.misc.BASE64Encoder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Auther: Hassion.Tang
 * @Date: 2021/3/25 13:50
 * @Description:excel操作工具类
 */
public class ExcelUtil {
    public static void download(HttpServletResponse response, HttpServletRequest request, String fileName, String[]   columnNames ){
        XSSFWorkbook workbook =new XSSFWorkbook();
        XSSFSheet sheet=workbook.createSheet();
        XSSFRow header1=sheet.createRow(0);
        for (int i=0;i<columnNames.length;i++){
            header1.createCell(i).setCellValue(columnNames[i]);
        }
        response.reset();
        try {
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + new String(fileName.getBytes(), "iso8859-1") + ".xlsx");
            OutputStream os = null;
            try {
                os = new BufferedOutputStream(response.getOutputStream());
                workbook.write(os);
                os.flush();
                os.close();
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }



        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }

    /**
     * Description: 读取excel文件数据
     * @Param: [file]
     * @Return: java.util.List<java.lang.Object[]>
     * @Author: Hassan.Tang
     * @Date: 2020/6/28 16:34
     **/
    public static List<Object[]> getExcelData(File file) {
        String name = file.getName();
        String type = name.substring(name.lastIndexOf("."));
        if ((".xls").equals(type)) {
            try {
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
                return readOldExcel(hssfWorkbook);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else if (".xlsx".equals(type)) {
            try {
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
                return readExcel(xssfWorkbook);
            } catch (InvalidFormatException | IOException e) {
                e.printStackTrace();
            }
        }

        return getData(file).get(0);
    }

    /**
     * Description: 获取数据
     * @Param: [file]
     * @Return: java.util.List<java.util.List<java.lang.Object[]>>
     * @Author: Hassan.Tang
     * @Date: 2020/6/28 16:35
     **/
    private static List<List<Object[]>> getData(File file) {
        HSSFWorkbook workbook;
        List<List<Object[]>> data = new ArrayList<List<Object[]>>();
        try {
            workbook = new HSSFWorkbook(new FileInputStream(file));
            HSSFSheet sheet = null;
            // 循环sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                sheet = workbook.getSheetAt(i);
                List<Object[]> rows = new ArrayList<Object[]>();
                int colsnum = 0;
                // 循环每一行
                for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                    HSSFRow row = sheet.getRow(j);
                    if (null != row) {
                        // 列数以excel第二行为准，第二行为标题，第一行为excel导入提示信息
                        colsnum = sheet.getRow(1).getPhysicalNumberOfCells();
                        Object[] cols = new Object[colsnum];
                        // 循环每一个单元格，以一行为单位，组成一个数组
                        for (int k = 0; k < colsnum; k++) {
                            // 判断单元格是否为null，若为null，则置空
                            if (null != row.getCell(k)) {
                                int type = row.getCell(k).getCellType();
                                // 判断单元格数据是否为数字
                                if (type == HSSFCell.CELL_TYPE_NUMERIC) {
                                    // 判断该数字的计数方法是否为科学计数法，若是，则转化为普通计数法
                                    if (String.valueOf(row.getCell(k).getNumericCellValue()).matches(".*[E|e].*")) {
                                        DecimalFormat df = new DecimalFormat("#.#");
                                        // 指定最长的小数点位为10
                                        df.setMaximumFractionDigits(10);
                                        cols[k] = df.format(row.getCell(k).getNumericCellValue());
                                        // 判断该数字是否是日期,若是则转成字符串
                                    } else if (HSSFDateUtil.isCellDateFormatted(row.getCell(k))) {
                                        Date d = row.getCell(k).getDateCellValue();
                                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                                        cols[k] = formater.format(d);
                                    } else {
                                        cols[k] = (row.getCell(k) + "").trim();
                                    }
                                } else {
                                    cols[k] = (row.getCell(k) + "").trim();
                                }
                            } else {
                                cols[k] = "";
                            }
                        }
                        // 以一行为单位，加入list
                        rows.add(cols);
                    }
                }
                // 返回所有数据，第一个list表示sheet，第二个list表示sheet内所有行数据，第三个string[]表示单元格数据
                data.add(rows);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return data;
    }


    /**
     * Description: 处理2007之前版本的excel  后缀名xls结尾
     * @Param: [hssfWorkbook]
     * @Return: java.util.List<java.lang.Object[]>
     * @Author: Hassan.Tang
     * @Date: 2020/6/28 16:36
     **/
    private static List<Object[]> readOldExcel(HSSFWorkbook hssfWorkbook) {
        List<Object[]> data = new ArrayList<Object[]>();
        HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
        HSSFCell cell = null;
        HSSFRow row = null;
        for (int i = sheetAt.getFirstRowNum(); i < sheetAt.getPhysicalNumberOfRows(); i++) {
            row = sheetAt.getRow(i + 1);
            if (row == null) {
                continue;
            }
            System.out.print(i + 1 + "-->: ");
            Object[] objects = new Object[row.getLastCellNum()];
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                switch (cell.getCellTypeEnum()) {
                    case STRING:
                        objects[j] = cell.getStringCellValue();
                        System.out.print(cell.getStringCellValue());
                        System.out.print(", ");
                        break;
                    case _NONE:
                        objects[j] = "";
                        break;
                    case BOOLEAN:
                        objects[j] = cell.getBooleanCellValue();
                        System.out.print(cell.getBooleanCellValue());
                        System.out.print(", ");
                        break;
                    case NUMERIC:
                        // 处理double类型的 1.0===》1
                        DecimalFormat df = new DecimalFormat("0");
                        String s = df.format(cell.getNumericCellValue());
                        objects[j] = s;
                        System.out.print(s);
                        System.out.print(", ");
                        break;
                    default:
                        objects[j] = cell.toString();
                }
            }
            data.add(objects);
            System.out.println();
        }
        return data;
    }

    /**
     * Description: 处理2007之后版本的excel  后缀名xlsx
     * @Param: [xssfWorkbook]
     * @Return: java.util.List<java.lang.Object[]>
     * @Author: Hassan.Tang
     * @Date: 2020/6/28 16:37
     **/
    private static List<Object[]> readExcel(XSSFWorkbook xssfWorkbook) {
        List<Object[]> data = new ArrayList<Object[]>();
        // 获得excel第一个工作薄
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
        // 行
        XSSFRow row = null;
        // 列
        XSSFCell cell = null;
        for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
            // 获取每一行
            row = sheet.getRow(i + 1);
            // 判断是否出现空行
            if (row == null) {
                continue;
            }
            System.out.print(i + 1 + "-->: ");
            Object[] objects = new Object[row.getLastCellNum()];
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);

                if (cell == null) {
                    continue;
                }
                // 第一行数据
                switch (cell.getCellTypeEnum()) {
                    case STRING:
                        objects[j] = cell.getStringCellValue();
                        System.out.print(cell.getStringCellValue());
                        System.out.print(", ");
                        break;
                    case _NONE:
                        objects[j] = "";
                        break;
                    case BOOLEAN:
                        objects[j] = cell.getBooleanCellValue();
                        System.out.print(cell.getBooleanCellValue());
                        System.out.print(", ");
                        break;
                    case NUMERIC:
                        // 处理double类型的 1.0===》1
                        DecimalFormat df = new DecimalFormat("0");
                        String s = df.format(cell.getNumericCellValue());
                        objects[j] = s;
                        System.out.print(s);
                        System.out.print(", ");
                        break;
                    default:
                        objects[j] = cell.toString();
                }

            }
            data.add(objects);
            System.out.println();
        }
        return data;
    }


    /**
     * Description: 将MultipartFile转化为file
     * @Param: [file]
     * @Return: java.io.File
     * @Author: Hassan.Tang
     * @Date: 2020/6/28 21:27
     **/
    public static File multipartFileToFile(MultipartFile file) throws Exception {

        File toFile = null;
        if (file.equals("") || file.getSize() <= 0) {
            file = null;
        } else {
            InputStream ins = null;
            ins = file.getInputStream();
            toFile = new File(file.getOriginalFilename());
            inputStreamToFile(ins, toFile);
            ins.close();
        }
        return toFile;
    }

    //获取流文件
    private static void inputStreamToFile(InputStream ins, File file) {
        try {
            OutputStream os = new FileOutputStream(file);
            int bytesRead = 0;
            byte[] buffer = new byte[8192];
            while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            ins.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    /**
     * @Description 将file转化为base64
      * @param:
      * @return:
      * @auther: Hassion.Tang
      * @date: 2021/4/28 14:46
     **/
    public static String fileToBase64(File file) {
        FileInputStream inputFile;
        try {
            inputFile = new FileInputStream(file);
            byte[] buffer = new byte[(int) file.length()];
            inputFile.read(buffer);
            inputFile.close();
            return new BASE64Encoder().encode(buffer);
        } catch (Exception e) {
            throw new RuntimeException("无法打开文件\n" + e.getMessage());
        }
    }


    /**
     * @Description   将 base64 转化为 file
      * @param:
      * @return:
      * @auther: Hassion.Tang
      * @date: 2021/4/28 14:46
     **/
    public static  boolean base64ToFile(String base64, String path) {
        byte[] buffer;
        try {
            buffer = new BASE64Decoder().decodeBuffer(base64);
            FileOutputStream out = new FileOutputStream(path);
            out.write(buffer);
            out.close();
            return true;
        } catch (Exception e) {
            throw new RuntimeException("base64字符串异常或地址异常\n" + e.getMessage());
        }
    }
}
